from odoo import models, fields, tools


class DeliveryAreaStatisticsReport(models.Model):
    _name = 's.delivery.area.statistics.report'
    _description = "Delivery Area Statistics Report"
    _auto = False

    date_index = fields.Integer(string='Date Index')
    row_index = fields.Integer(string='Row Index')
    order_type = fields.Selection([('0', 'WTT Self-Order'), ('1', 'JOVE Transfer Order')], string='Order Type')
    product_type = fields.Selection([('0', 'Negative'), ('1', 'Positive')], string='Product Type')
    order_year = fields.Integer(string='Year')
    order_month = fields.Integer(string='Month')
    order_date = fields.Char(string='Order Date')
    order_area = fields.Float(string='Order Area (m^2)', digits=(16, 2))

    def init(self):
        tools.drop_view_if_exists(self._cr, 's_delivery_area_statistics_report')
        query = """
        CREATE OR REPLACE VIEW s_delivery_area_statistics_report AS (
        -- 交期面积统计
SELECT
    row_number() over() as id,
    DENSE_RANK() OVER (ORDER BY LENGTH(SO.order_date) DESC, SO.order_date) AS date_index, -- 日期索引（用于生成Excel时的辅助列）
    CASE
        WHEN SO.order_type = '0' AND SO.product_type = '1' THEN 1
        WHEN SO.order_type = '0' AND SO.product_type = '0' THEN 2
        WHEN SO.order_type = '1' AND SO.product_type = '1' THEN 3
        WHEN SO.order_type = '1' AND SO.product_type = '0' THEN 4
        ELSE 0
    END AS row_index, -- 行索引（用于生成Excel时的辅助列）
    *
FROM
    (
        SELECT
            SO.orderType::character varying as order_type, -- 订单类型（0=WTT自接订单，1=JOVE转单订单）
            SO.productType::character varying as product_type, -- 流程（0=负片，1=正片）
            SO.orderYear as order_year, -- 交货日期（格式YYYY）
            SO.orderMonth as order_month, -- 交货日期（格式MM）
            CASE
                WHEN DATE_PART('year', CURRENT_DATE) >= SO.orderYear AND DATE_PART('month', CURRENT_DATE) > SO.orderMonth THEN
                    TO_CHAR((NOW() + INTERVAL '1 hour')::DATE, 'YYYY-MM"前"')
                ELSE
                    SO.orderDate
            END AS order_date, -- 交货日期（格式YYYY-MM）,未来的日期正常每个单独一组，过去的日期全部归类为 {当前年}-{当前月}前 组内
            SO.orderArea as order_area -- 订单面积（平米）
        FROM
            (
                SELECT
                    SO.orderType,
                    SO.productType,
                    SPLIT_PART(SO.orderDate, '-', 1)::integer AS orderYear,
                    SPLIT_PART(SO.orderDate, '-', 2)::integer AS orderMonth,
                    SO.orderDate,
                    ROUND(SUM(SO.orderArea)::numeric, 2) AS orderArea
                FROM
                    (
                        SELECT
                            CASE
                                WHEN rp.code LIKE '250004%' THEN 1
                                ELSE 0
                            END AS orderType,
                            CASE
                                WHEN ptpl.value_id = 2960 THEN 1
                                ELSE 0
                            END AS productType,
                            TO_CHAR((so.commitment_date + INTERVAL '1 hour')::DATE, 'YYYY-MM') AS orderDate, -- 返回的结果需要转回北京时间，所以小时加一
                            so.s_product_metre AS orderArea
                        FROM
                            sale_order AS so
                            LEFT JOIN res_partner AS rp ON rp.id = so.partner_id
                            LEFT JOIN product_template_param_line AS ptpl ON ptpl.product_tmpl_id = so.product_id
                                AND ptpl.attribute_id = 13961
                        WHERE
                            so.status != 'cancelled'
                            AND so.state != 'cancel'
                            AND so.create_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 hour' -- 获取本月的第一天，因为是以北京时间查询数据，所以小时减一
                            AND so.create_date <= CONCAT(CURRENT_DATE - 1, ' 23:59:59.999')::TIMESTAMP - INTERVAL '1 hour' -- 获取昨天日期，因为是以北京时间查询数据，所以小时减一
                    ) AS SO
                GROUP BY
                    SO.orderType,
                    SO.productType,
                    SO.orderDate
            ) AS SO
    ) AS SO
        )"""
        self._cr.execute(query)
